#!/bin/sh

CUR_PATH=$(cd "$(dirname "$0")"; pwd)
source ${CUR_PATH}/mysql_server.conf
curr_date=$1
curr_year=date -d ${curr_date} +%Y
next_year=$((curr_year+1))
suffix=date -d "${curr_date}" +%y
#echo $curr_date
#echo $curr_year
#echo $next_year
#echo $suffix

create_table_sql=$(cat <<EOF

DROP TABLE IF EXISTS gas_open_ranges_day_${suffix};

CREATE TABLE gas_open_ranges_day_${suffix} (
  id bigint(20) NOT NULL AUTO_INCREMENT COMMENT '表主键',
  uid bigint(20) NOT NULL COMMENT '唯一ID',
  tid bigint(20) DEFAULT NULL COMMENT '通讯号',
  gas_open_percent0_duration int(11) DEFAULT NULL COMMENT '油门开度=0时长，单位：秒',
  gas_open_percent10_duration int(11) DEFAULT NULL COMMENT '油门开度0<x<=10时长，单位：秒',
  gas_open_percent20_duration int(11) DEFAULT NULL COMMENT '油门开度10<x<=20时长，单位：秒',
  gas_open_percent30_duration int(11) DEFAULT NULL COMMENT '油门开度20<x<=30时长，单位：秒',
  gas_open_percent40_duration int(11) DEFAULT NULL COMMENT '油门开度30<x<=40时长，单位：秒',
  gas_open_percent50_duration int(11) DEFAULT NULL COMMENT '油门开度40<x<=50时长，单位：秒',
  gas_open_percent60_duration int(11) DEFAULT NULL COMMENT '油门开度50<x<=60时长，单位：秒',
  gas_open_percent70_duration int(11) DEFAULT NULL COMMENT '油门开度60<x<=70时长，单位：秒',
  gas_open_percent80_duration int(11) DEFAULT NULL COMMENT '油门开度70<x<=80时长，单位：秒',
  gas_open_percent90_duration int(11) DEFAULT NULL COMMENT '油门开度80<x<=90时长，单位：秒',
  gas_open_percent99_duration int(11) DEFAULT NULL COMMENT '油门开度90<x<100时长，单位：秒',
  gas_open_percent100_duration int(11) DEFAULT NULL COMMENT '油门开度=100时长，单位：秒',
  gas_open_percent_count int(11) DEFAULT NULL COMMENT '油门开度总个数',
  gas_open_percent_sum int(11) DEFAULT NULL COMMENT '油门开度总和',
  avg_gas_open_percent int(11) DEFAULT NULL COMMENT '平均油门开度，速度>0 转速大于0，油门开度总和/油门开度个数',
  update_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  day int(11) NOT NULL COMMENT 'yyyyMMdd 数据时间',
  PRIMARY KEY (id,day),
  UNIQUE KEY uid_day_index (uid,day) USING BTREE,
  KEY day (day) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=100000014 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=COMPACT
 PARTITION BY RANGE (day)
(PARTITION p${curr_year}0101 VALUES LESS THAN (${curr_year}0201) ENGINE = InnoDB,
 PARTITION p${curr_year}0201 VALUES LESS THAN (${curr_year}0301) ENGINE = InnoDB,
 PARTITION p${curr_year}0301 VALUES LESS THAN (${curr_year}0401) ENGINE = InnoDB,
 PARTITION p${curr_year}0401 VALUES LESS THAN (${curr_year}0501) ENGINE = InnoDB,
 PARTITION p${curr_year}0501 VALUES LESS THAN (${curr_year}0601) ENGINE = InnoDB,
 PARTITION p${curr_year}0601 VALUES LESS THAN (${curr_year}0701) ENGINE = InnoDB,
 PARTITION p${curr_year}0701 VALUES LESS THAN (${curr_year}0801) ENGINE = InnoDB,
 PARTITION p${curr_year}0801 VALUES LESS THAN (${curr_year}0901) ENGINE = InnoDB,
 PARTITION p${curr_year}0901 VALUES LESS THAN (${curr_year}1001) ENGINE = InnoDB,
 PARTITION p${curr_year}1001 VALUES LESS THAN (${curr_year}1101) ENGINE = InnoDB,
 PARTITION p${curr_year}1101 VALUES LESS THAN (${curr_year}1201) ENGINE = InnoDB,
 PARTITION p${curr_year}1201 VALUES LESS THAN (${next_year}0101) ENGINE = InnoDB) ;


CREATE TABLE gas_open_ranges_week_${suffix} (
  id bigint(20) NOT NULL AUTO_INCREMENT COMMENT '表主键',
  uid bigint(20) NOT NULL COMMENT '唯一ID',
  tid bigint(20) DEFAULT NULL COMMENT '通讯号',
  gas_open_percent0_duration int(11) DEFAULT NULL COMMENT '油门开度=0时长，单位：秒',
  gas_open_percent10_duration int(11) DEFAULT NULL COMMENT '油门开度0<x<=10时长，单位：秒',
  gas_open_percent20_duration int(11) DEFAULT NULL COMMENT '油门开度10<x<=20时长，单位：秒',
  gas_open_percent30_duration int(11) DEFAULT NULL COMMENT '油门开度20<x<=30时长，单位：秒',
  gas_open_percent40_duration int(11) DEFAULT NULL COMMENT '油门开度30<x<=40时长，单位：秒',
  gas_open_percent50_duration int(11) DEFAULT NULL COMMENT '油门开度40<x<=50时长，单位：秒',
  gas_open_percent60_duration int(11) DEFAULT NULL COMMENT '油门开度50<x<=60时长，单位：秒',
  gas_open_percent70_duration int(11) DEFAULT NULL COMMENT '油门开度60<x<=70时长，单位：秒',
  gas_open_percent80_duration int(11) DEFAULT NULL COMMENT '油门开度70<x<=80时长，单位：秒',
  gas_open_percent90_duration int(11) DEFAULT NULL COMMENT '油门开度80<x<=90时长，单位：秒',
  gas_open_percent99_duration int(11) DEFAULT NULL COMMENT '油门开度90<x<100时长，单位：秒',
  gas_open_percent100_duration int(11) DEFAULT NULL COMMENT '油门开度=100时长，单位：秒',
  gas_open_percent_count int(11) DEFAULT NULL COMMENT '油门开度总个数',
  gas_open_percent_sum int(11) DEFAULT NULL COMMENT '油门开度总和',
  avg_gas_open_percent int(11) DEFAULT NULL COMMENT '平均油门开度，速度>0 转速大于0，油门开度总和/油门开度个数',
  update_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  day int(11) NOT NULL COMMENT 'yyyyMMdd 数据时间',
  PRIMARY KEY (id,day),
  UNIQUE KEY uid_day_index (uid,day) USING BTREE,
  KEY day (day) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=100000014 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=COMPACT
 PARTITION BY RANGE (day)
(PARTITION p${curr_year}0101 VALUES LESS THAN (${curr_year}0201) ENGINE = InnoDB,
 PARTITION p${curr_year}0201 VALUES LESS THAN (${curr_year}0301) ENGINE = InnoDB,
 PARTITION p${curr_year}0301 VALUES LESS THAN (${curr_year}0401) ENGINE = InnoDB,
 PARTITION p${curr_year}0401 VALUES LESS THAN (${curr_year}0501) ENGINE = InnoDB,
 PARTITION p${curr_year}0501 VALUES LESS THAN (${curr_year}0601) ENGINE = InnoDB,
 PARTITION p${curr_year}0601 VALUES LESS THAN (${curr_year}0701) ENGINE = InnoDB,
 PARTITION p${curr_year}0701 VALUES LESS THAN (${curr_year}0801) ENGINE = InnoDB,
 PARTITION p${curr_year}0801 VALUES LESS THAN (${curr_year}0901) ENGINE = InnoDB,
 PARTITION p${curr_year}0901 VALUES LESS THAN (${curr_year}1001) ENGINE = InnoDB,
 PARTITION p${curr_year}1001 VALUES LESS THAN (${curr_year}1101) ENGINE = InnoDB,
 PARTITION p${curr_year}1101 VALUES LESS THAN (${curr_year}1201) ENGINE = InnoDB,
 PARTITION p${curr_year}1201 VALUES LESS THAN (${next_year}0101) ENGINE = InnoDB) ;

 CREATE TABLE gas_open_ranges_month_${suffix} (
   id bigint(20) NOT NULL AUTO_INCREMENT COMMENT '表主键',
   uid bigint(20) NOT NULL COMMENT '唯一ID',
   tid bigint(20) DEFAULT NULL COMMENT '通讯号',
   gas_open_percent0_duration int(11) DEFAULT NULL COMMENT '油门开度=0时长，单位：秒',
   gas_open_percent10_duration int(11) DEFAULT NULL COMMENT '油门开度0<x<=10时长，单位：秒',
   gas_open_percent20_duration int(11) DEFAULT NULL COMMENT '油门开度10<x<=20时长，单位：秒',
   gas_open_percent30_duration int(11) DEFAULT NULL COMMENT '油门开度20<x<=30时长，单位：秒',
   gas_open_percent40_duration int(11) DEFAULT NULL COMMENT '油门开度30<x<=40时长，单位：秒',
   gas_open_percent50_duration int(11) DEFAULT NULL COMMENT '油门开度40<x<=50时长，单位：秒',
   gas_open_percent60_duration int(11) DEFAULT NULL COMMENT '油门开度50<x<=60时长，单位：秒',
   gas_open_percent70_duration int(11) DEFAULT NULL COMMENT '油门开度60<x<=70时长，单位：秒',
   gas_open_percent80_duration int(11) DEFAULT NULL COMMENT '油门开度70<x<=80时长，单位：秒',
   gas_open_percent90_duration int(11) DEFAULT NULL COMMENT '油门开度80<x<=90时长，单位：秒',
   gas_open_percent99_duration int(11) DEFAULT NULL COMMENT '油门开度90<x<100时长，单位：秒',
   gas_open_percent100_duration int(11) DEFAULT NULL COMMENT '油门开度=100时长，单位：秒',
   gas_open_percent_count int(11) DEFAULT NULL COMMENT '油门开度总个数',
   gas_open_percent_sum int(11) DEFAULT NULL COMMENT '油门开度总和',
   avg_gas_open_percent int(11) DEFAULT NULL COMMENT '平均油门开度，速度>0 转速大于0，油门开度总和/油门开度个数',
   update_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
   day int(11) NOT NULL COMMENT 'yyyyMMdd 数据时间',
   PRIMARY KEY (id,day),
   UNIQUE KEY uid_day_index (uid,day) USING BTREE,
   KEY day (day) USING BTREE
 ) ENGINE=InnoDB AUTO_INCREMENT=100000014 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=COMPACT
  PARTITION BY RANGE (day)
 (PARTITION p${curr_year}0101 VALUES LESS THAN (${curr_year}0201) ENGINE = InnoDB,
  PARTITION p${curr_year}0201 VALUES LESS THAN (${curr_year}0301) ENGINE = InnoDB,
  PARTITION p${curr_year}0301 VALUES LESS THAN (${curr_year}0401) ENGINE = InnoDB,
  PARTITION p${curr_year}0401 VALUES LESS THAN (${curr_year}0501) ENGINE = InnoDB,
  PARTITION p${curr_year}0501 VALUES LESS THAN (${curr_year}0601) ENGINE = InnoDB,
  PARTITION p${curr_year}0601 VALUES LESS THAN (${curr_year}0701) ENGINE = InnoDB,
  PARTITION p${curr_year}0701 VALUES LESS THAN (${curr_year}0801) ENGINE = InnoDB,
  PARTITION p${curr_year}0801 VALUES LESS THAN (${curr_year}0901) ENGINE = InnoDB,
  PARTITION p${curr_year}0901 VALUES LESS THAN (${curr_year}1001) ENGINE = InnoDB,
  PARTITION p${curr_year}1001 VALUES LESS THAN (${curr_year}1101) ENGINE = InnoDB,
  PARTITION p${curr_year}1101 VALUES LESS THAN (${curr_year}1201) ENGINE = InnoDB,
  PARTITION p${curr_year}1201 VALUES LESS THAN (${next_year}0101) ENGINE = InnoDB) ;

EOF
)
#echo ${create_table_sql}
mysql -h${HOSTNAME}  -P${PORT}  -u${USERNAME} -p${PASSWORD} ${DBNAME} -e "${create_table_sql}"
if [ $? -ne 0 ] ; then
  echo "表创建失败"
else
  echo "表创建成功"
fi
